package com.windaka.suizhi.utils;


import com.windaka.suizhi.annotation.ExcelInfo;
import com.windaka.suizhi.annotation.ExcelSheet;
import com.windaka.suizhi.model.excel.BaseExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;


/**
 * <p>
 * <strong>Excel工具类</strong>
 * </p>
 * <li>
 * <p>
 * <strong>示例1: 将数据导出到excel</strong> <br>
 * 需要传入的参数: String filePath(存储的路径和名称)、List<? extends BaseExcel> dataList 传入的数据
 * Boolean success = ExcelUtils.dataToExcel(filePath, dataList);
 * 结果: success: true 以及 将excel写出到本地
 * </p>
 * </li>
 * <li>
 * <p>
 * <strong>示例2: 将excel 读入到List</strong> <br>
 * List<Model> list = ExcelUtils.excelToData(filePath, Model.class);
 * 结果: List<Model> list
 * </p>
 * </li>
 * @author wzk
 * @see ExcelInfo
 * @see XSSFWorkbook
 * @see XSSFSheet
 * @see XSSFRow
 * @see XSSFCell
 * @since 11:41 2021/11/19
 **/
@Slf4j
@SuppressWarnings("unchecked")
public class ExcelUtils {

    private ExcelUtils() {
    }

    private static final String DEFAULT_SHEET_NAME = "sheet";

    /**
     * <li>
     * <p>
     * <strong>示例1: 将数据导出到excel</strong> <br>
     * Boolean success = ExcelUtils.dataToExcel(filePath, dataList);
     * 结果: success: true 以及 将excel写出到本地
     * </p>
     * </li>
     **/
    public static Boolean dataToExcel(String filePath, List<? extends BaseExcel> dataList) throws IOException {
        // 获取字段注解上的排序
        Object object = dataList.get(0);
        // 拿到定义的字段
        // 遍历字段 通过注解拿到 字段的相关信息
        // 这里通过排序 给字段排序
        Field[] fields = object.getClass().getDeclaredFields();
        Arrays.sort(fields, (o1, o2) -> {
            ExcelInfo infoI = o1.getAnnotation(ExcelInfo.class);
            ExcelInfo infoJ = o2.getAnnotation(ExcelInfo.class);
            return infoI.number() - infoJ.number();
        });

        // 排序之后 字段已经按照理想状况排列
        // 下面开始写出
        // 初始化Excel
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet(DEFAULT_SHEET_NAME);
        // 写标题
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < fields.length; i++) {
            // 通过注解获取标题
            Field field = fields[i];
            ExcelInfo info = field.getAnnotation(ExcelInfo.class);
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(info.title());
        }

        // 将dataList的数据写入到每一行
        for (int i = 0; i < dataList.size(); i++) {
            // dataList每个对象为一行
            Object eachObject = dataList.get(i);
            XSSFRow objectRow = sheet.createRow(i + 1);
            // 定义的字段
            for (int j = 0; j < fields.length; j++) {
                Field field = fields[j];
                // 每个字段为一列
                XSSFCell cells = objectRow.createCell(j);
                try {
                    // 反射执行 获取值
                    Method method = eachObject.getClass().getMethod("get" + toFirstUp(field.getName()));
                    Object cellValue = method.invoke(eachObject);
                    cells.setCellValue(null == cellValue ? "" : cellValue.toString());
                } catch (Exception e) {
                    cells.setCellValue("");
                    e.printStackTrace();
                }
            }
        }

        // 保存写出
        File file = new File(filePath);
        try (FileOutputStream fileOutputStream = new FileOutputStream(file)) {
            workBook.write(fileOutputStream);
        } catch (IOException e) {
            log.error("[ExcelUtils => dataToExcel 导出失败! {}]", e.getMessage());
            return false;
        } finally {
            workBook.close();
        }
        return true;
    }

    /**
     * <li>
     * <p>
     * <strong>示例2: 将excel 读入到List</strong> <br>
     * List<Model> list = ExcelUtils.excelToData(filePath, Model.class);
     * 结果: List<Model> list
     * @author wzk
     * </p>
     * </li>
     **/
    public static <T> T excelToData(String filePath, Class<? extends BaseExcel> clazz) throws IOException, ReflectiveOperationException {
        // 获取SheetName
        ExcelSheet excelSheet = clazz.getAnnotation(ExcelSheet.class);
        String sheetName = excelSheet.name();

        // 获取字段对应信息
        Field[] fields = clazz.getDeclaredFields();
        Map<String, String> title2Field = new HashMap<>(fields.length);
        for (Field field : fields) {
            ExcelInfo excelInfo = field.getAnnotation(ExcelInfo.class);
            String fieldName = field.getName();
            // 获得set的方法名称
            title2Field.put(excelInfo.title(), fieldName);
        }

        // 处理结果
        List<Object> dataList = new ArrayList<>();

        // 获取Excel文件
        File file = new File(filePath);
        try(InputStream inputStream = new FileInputStream(file);
            // 读取Excel
            XSSFWorkbook sheets = new XSSFWorkbook(inputStream)
        ) {
            // 获取 SheetName 对应的 Sheet 页
            XSSFSheet sheet = sheets.getSheet(sheetName);
            // 第0行是标题
            XSSFRow titleRow = sheet.getRow(0);
            // 遍历所有行
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i ++) {
                // 反射新建对象 对应一行excel
                Object eachObject = clazz.getDeclaredConstructor().newInstance();
                // 获取当前行
                XSSFRow row = sheet.getRow(i);
                // 读取每一格内容
                for (int j = 0; j < row.getPhysicalNumberOfCells(); j ++) {
                    // 获取该列的标题
                    XSSFCell titleCell = titleRow.getCell(j);
                    String title = titleCell.getStringCellValue();
                    if (!title2Field.containsKey(title)) {
                        continue;
                    }
                    // 获取该列的值
                    XSSFCell cell = row.getCell(j);
                    cell.setCellType(CellType.STRING);

                    // 获取 title 对应的 field
                    String fieldName = title2Field.get(titleCell.getStringCellValue());
                    Method method = eachObject.getClass().getMethod("set" + toFirstUp(fieldName), String.class);
                    method.invoke(eachObject, cell.getStringCellValue());
                }
                // 存入dataList
                dataList.add(eachObject);
            }
        }
        return (T) dataList;
    }

    /**
     * 首字母大写
     * @author wzk
     * @date 13:44 2021/11/23
     **/
    private static String toFirstUp(String str) {
        char[] cs = str.toCharArray();
        cs[0] -= 32;
        return String.valueOf(cs);
    }

    /**
     * Excel 根据模板 替换模板内容 导出Excel
     *
     * @param inputStream 文件模板流
     * @param model       数据
     * @return XSSFWorkbook
     * @author Lee
     * @date 2022/5/21 10:59
     */
    public static XSSFWorkbook exportExcelByTemplate(InputStream inputStream, Map<String, String> model) {
        // 获取占位符的keySet
        Set<String> keySet = model.keySet();
        // 操作IO
        XSSFWorkbook xssfWorkbook;
        try { // excel
            xssfWorkbook = new XSSFWorkbook(inputStream);
            int i = 0;
            int j = 0;
            XSSFSheet sheet = xssfWorkbook.getSheet(DEFAULT_SHEET_NAME);
            while (sheet.getRow(i) != null) {
                while (sheet.getRow(i).getCell(j) != null) {
                    for (String s : keySet) {
                        if (sheet.getRow(i).getCell(j).getStringCellValue().equals(s)) {
                            sheet.getRow(i).getCell(j).setCellValue(model.getOrDefault(s, ""));
                        }
                    }
                    j++;
                }
                j = 0;
                i++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return xssfWorkbook;
    }

    /**
     * Excel 根据模板 替换模板内容 导出Excel
     *
     * @param inputStream 文件模板流
     * @param model       数据
     * @param num       从第几行开始写入
     * @return XSSFWorkbook
     * @author Lee
     * @date 2022/5/21 10:59
     */
    public static XSSFWorkbook exportExcelByTemplate(InputStream inputStream, List<LinkedHashMap<String, String>> model, int num) {
        // 操作IO
        XSSFWorkbook xssfWorkbook;
        try { // excel
            xssfWorkbook = new XSSFWorkbook(inputStream);
            int i = 0;
            XSSFSheet sheet = xssfWorkbook.getSheet(DEFAULT_SHEET_NAME);
            XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            for (Map<String, String> map : model) {
                //创建Excel工作表的行
                XSSFRow row = sheet.createRow((short) num);
                Set<Map.Entry<String, String>> entries = map.entrySet();
                for (Map.Entry<String, String> entry : entries) {
                    XSSFCell cell = row.createCell(i);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(entry.getValue());
                    i++;
                }
                i = 0;
                num++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return xssfWorkbook;
    }
}
